package com.wanwanbookstore.dao;

import com.wanwanbookstore.entity.BookCategory;
import com.wanwanbookstore.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookCategoryDAO {
    // 获取所有分类
    public List<BookCategory> getAllCategories() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<BookCategory> categories = new ArrayList<>();
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM book_category ORDER BY name";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                BookCategory category = new BookCategory();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setCreateTime(rs.getTimestamp("create_time"));
                category.setIsRecommended(rs.getInt("is_recommended"));
                categories.add(category);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
            try {
                if (stmt != null) stmt.close();
                if (rs != null) rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return categories;
    }

    // 添加新分类
    public boolean addCategory(String name) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO book_category (name, is_recommended) VALUES (?, 0)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, name);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    // 根据名称获取分类
    public BookCategory getCategoryByName(String name) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM book_category WHERE name = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, name);
            rs = stmt.executeQuery();
            if (rs.next()) {
                BookCategory category = new BookCategory();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setCreateTime(rs.getTimestamp("create_time"));
                category.setIsRecommended(rs.getInt("is_recommended"));
                return category;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
            try {
                if (stmt != null) stmt.close();
                if (rs != null) rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    
    // 获取推荐的分类
    public List<BookCategory> getRecommendedCategories() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<BookCategory> categories = new ArrayList<>();
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM book_category WHERE is_recommended = 1 ORDER BY name";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                BookCategory category = new BookCategory();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setCreateTime(rs.getTimestamp("create_time"));
                category.setIsRecommended(rs.getInt("is_recommended"));
                categories.add(category);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
            try {
                if (stmt != null) stmt.close();
                if (rs != null) rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return categories;
    }
    
    // 更新分类的推荐状态
    public boolean updateCategoryRecommendedStatus(int categoryId, int isRecommended) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE book_category SET is_recommended = ? WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, isRecommended);
            stmt.setInt(2, categoryId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    
    // 获取推荐分类的数量
    public int getRecommendedCategoriesCount() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM book_category WHERE is_recommended = 1";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
            try {
                if (stmt != null) stmt.close();
                if (rs != null) rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
}